if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOICompleteQuestionnaireAnswerList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCOICompleteQuestionnaireAnswerList]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
* Name:			GetCOICompleteQuestionnaireAnswerList
* Purpose:		Retrieves a list of QuestionnaireAnswer based on the COI's
*					id passed in as a parameter.
*
* PARAMETERS
* Name    		Description					
* -------------  	-------------------------------------------
* @coiId		The Id of the COI whose QuestionnaireAnswers are to be retrieved.
******************************************************************/

CREATE Procedure [dbo].[GetCOICompleteQuestionnaireAnswerList] (
		@coiId		INTEGER	
)
AS
BEGIN

	SET NOCOUNT ON

select QuestionnaireAnswerId, Description, AllowMultiple, QuestionnaireVersionId, Name, QuestionnaireCategory, LastModifiedDate, ExternalId, Q.CategorySequenceNo, Status, StatusMeaning
from
	((SELECT '0' as QuestionnaireAnswerId, Q.Description, Q.AllowMultiple, QV.Id as QuestionnaireVersionId, Q.[Name], Q.Category as QuestionnaireCategory, null as LastModifiedDate, null as ExternalId, Q.CategorySequenceNo, null as Status, null as StatusMeaning
	FROM Questionnaire Q INNER JOIN QuestionnaireVersion QV ON QV.QuestionnaireId = Q.Id
	WHERE QV.Active = 1
	AND Q.Category LIKE 'QUESTCAT_COI_%'
	)

	UNION

	(SELECT QA.Id as QuestionnaireAnswerId, Q.Description, Q.AllowMultiple, QA.QuestionnaireVersionId, QA.[Name], Q.Category as QuestionnaireCategory, QA.LastModifiedDAte, QA.ExternalId, Q.CategorySequenceNo, QA.Status, RC.RefMeaning as StatusMeaning
	FROM QuestionnaireAnswer QA INNER JOIN COI_QuestionnaireAnswer_Map Map ON Map.QuestionnaireAnswerId = QA.Id
		INNER JOIN ConflictOfInterest COI ON COI.Id = Map.COIId 
		INNER JOIN QuestionnaireVersion QV ON QA.QuestionnaireVersionId = QV.Id
		INNER JOIN Questionnaire Q ON QV.QuestionnaireId = Q.Id
		LEFT JOIN RefCode RC ON RC.RefCode = QA.Status
	WHERE COI.Id = @coiId
	)) Q
ORDER BY Q.QuestionnaireCategory, Q.CategorySequenceNo


END


GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF  
